﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using CommClass;

namespace DMS
{
    public partial class FrmFareDoImport : DMS.FrmTemplate
    {
        DataConnection dc = new DataConnection();
        DataSource dsExcelFile;
        MO mo = new MO(App.Ds);
        public FrmFareDoImport()
        {
            InitializeComponent();
        }

        private void FrmFareDoImport_Load(object sender, EventArgs e)
        {

        }

        //检查EXCEL数据是否符合导入要求
        public string checkExcelData(DataTable dt)
        {
            string donumber = "";
            DataRow[] dr;
            int carrierid;
            int prodid;
            int areaid;
            int addressid;

            for (int j = 0; j < dt.Rows.Count; j++)
            {
                carrierid = mo.CheckCarrierID(dt.Rows[j][12].ToString());
                prodid = mo.CheckProdID(dt.Rows[j][9].ToString());
                areaid = mo.checkAreaid(dt.Rows[j][14].ToString());
                addressid = mo.CheckAddressid(dt.Rows[j][6].ToString());
                dr = dt.Select("物料代码='" + dt.Rows[j][9].ToString()+ "'");
                DataTable dpt = new DataTable();
                dpt = dr.CopyToDataTable();



                //承运商编号,产品编号,区域编号，送货地址编号都能匹配到
                if (carrierid > 0 && prodid > 0 && areaid > 0 && addressid >0)
                {
                    //判断源数据提单和产品是否重复
                    if (dpt.Rows.Count > 1)
                    {
                        listinfo.Items.Add(dt.Rows[j][1] + "该提单源数据重复被忽略。");
                        donumber = dt.Rows[0][1].ToString();
                        break;
                    }
                    else
                    {

                        //判断提单是否已存在
                        if (App.checkFareDoNumber(dt.Rows[0][1].ToString()))
                        {

                            listinfo.Items.Add(dt.Rows[0][1] + "该提单系统中已存在被忽略。");
                            donumber = dt.Rows[0][1].ToString();
                            break;

                        }

                    }

                }
                else
                {
                    listinfo.Items.Add("提单:" + dt.Rows[j][1] + ",承运商:" + dt.Rows[j][12] + ",产品代码:" + dt.Rows[j][9] + ",区域：" + dt.Rows[j][14] +",送货地址："+dt.Rows[j][6]+ "与系统不符，请核实。");
                    donumber = dt.Rows[0][1].ToString();
                    continue;

                }

            }
            return donumber;

        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();

            dlg.Filter = "Excel文件|*.xls;*.xlsx|所有文件|*.*";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                this.tbFileName.Text = dlg.FileName;
                dlg.Dispose();
                dc.DBName = tbFileName.Text;
                dsExcelFile = new ExcelDataSource(dc);
                cbbSheets.DataSource = dsExcelFile.GetTableNames();
                cbbSheets.SelectedIndex = 0;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //显示excel数据
            dvgDO.DataSource = dsExcelFile.GetRecord("select * from [" + cbbSheets.Text + "] order by 提单号 ").Tables[0];

            this.button3.Enabled = true;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedIndex = 1;
            listinfo.Items.Add("开始导入数据...");
            ////获取excel数据
            DataTable dtt = (DataTable)dvgDO.DataSource;

            DeliveryOrder d = new DeliveryOrder();
            DOEntry de = new DOEntry();
            string deliverynumber;

            //遍历excel数据并上传至数据库
            int i = 0;
            int carrierid;
            int areaid;

            while (i < dtt.Rows.Count)
            {
                DataRow[] dr;
                DataTable dt;
                dr = dtt.Select("提单号='" + dtt.Rows[i][1].ToString() + "'");
                dt = dr.CopyToDataTable();
                deliverynumber = checkExcelData(dt);
                if (deliverynumber == "")
                {
                    carrierid = mo.CheckCarrierID(dt.Rows[0][12].ToString());
                    areaid = mo.checkAreaid(dt.Rows[0][14].ToString());
                    d.DeliveryNumber = dt.Rows[0][1].ToString();
                    d.OrderNumber = dt.Rows[0][0].ToString();
                    d.OrderType = dt.Rows[0][2].ToString();
                    d.SaleType = dt.Rows[0][20].ToString();
                    d.PONumber = dt.Rows[0][3].ToString();
                    d.OrderDatetime = DateTime.Parse(dt.Rows[0][16].ToString());
                    d.SoldTo = dt.Rows[0][4].ToString();
                    d.DeliveryTo = dt.Rows[0][5].ToString();
                    d.CustName = dt.Rows[0][4].ToString();
                    d.DeliveryAddress = mo.CheckAddressid(dt.Rows[0][6].ToString()).ToString ();
                    d.CarrierID = carrierid;
                    d.Remark = dt.Rows[0][13].ToString();
                    d.AreaID = areaid;
                    d.VehicleNumber = dt.Rows[0][19].ToString();
                    if (mo.SaveFareDoHeard(d))
                    {

                        for (int j = 0; j < dt.Rows.Count; j++)
                        {
                            de.ProdID = mo.CheckProdID(dt.Rows[j][9].ToString());

                            de.OrderQty = double.Parse(dt.Rows[j][17].ToString());
                            de.Weight = double.Parse(dt.Rows[j][18].ToString());
                            de.Batch = dt.Rows[j][21].ToString();
                            if (!mo.SaveFareDoEntry(de, d))
                            {

                                mo.DeleteFareDo(d.DeliveryNumber);
                                listinfo.Items.Add("提单：" + d.DeliveryNumber + "导入失败！");
                                break;
                            }


                        }
                    }

                }
                i = i + dt.Rows.Count;//跳到下一个需要导入的提单
            }

            listinfo.Items.Add("数据导入完成！");

        }

        private void button4_Click(object sender, EventArgs e)
        {
            Close();
        }

    }
}
